# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface


class Scrapy09258TcPipeline:

    # 爬虫开始执行就执行的方法
    def open_spider(self, spider):
        print('爬虫开始执行就执行的方法--open_spider---------')
        self.fp = open('book.json', 'w', encoding='utf-8')

    # item就是yield后面的car对象
    def process_item(self, item, spider):
        print('收到car数据...')
        self.fp.write(str(item))
        return item

    def close_spider(self, spider):
        print('关闭文件对象')
        self.fp.close()


# 多条管道
# 1. 定义管道
# 2. 在settings中开启管道
import urllib.request


class Scrapy09258DownImgPipeline:
    def process_item(self, item, spider):
        print('图片开始下载')
        url = item.get('src_url')
        file_name = './books/' + item.get('name') + '.jpg'
        urllib.request.urlretrieve(url=url, filename=file_name)
        return item


# 将数据保存到mysql数据库中
# 加载settings文件
from scrapy.utils.project import get_project_settings
import pymysql


class MysqlPipeline:

    def open_spider(self, spider):
        settings = get_project_settings()
        self.host = settings['DB_HOST']
        self.port = settings['DB_PORT']
        self.user = settings['DB_USER']
        self.password = settings['DB_PASSWROD']
        self.name = settings['DB_NAME']
        self.charset = settings['DB_CHARSET']

        self.connect()

    def connect(self):
        self.conn = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            db=self.name,
            charset=self.charset
        )
        self.cursor = self.conn.cursor()

    def process_item(self, item, spider):
        sql = 'insert into ldp_book(name,price,src_url) values("{}","{}","{}")'.format(item['name'], item['price'],
                                                                                       item['src_url'])

        print('sql=', sql)
        # 执行sql语句
        self.cursor.execute(sql)
        # 提交
        self.conn.commit()
        return item

    def close_spider(self, spider):
        self.cursor.close()
        self.conn.close()
